/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package javaapplication2;

import java.util.List;
import org.jdom.*;
import org.jdom.input.*;
import org.jdom.output.*;
import java.sql.*;
/**
 *
 * @author Marko
 */
public class parseToBD {
    public static void main(String args[]){
        try {
          Class.forName("com.mysql.jdbc.Driver");
          String connectionUrl = "jdbc:mysql://localhost/poi?" +
                                   "user=mrc&password=mrc";
          Connection con = DriverManager.getConnection(connectionUrl);
          Statement stmt = con.createStatement();
          SAXBuilder builder = new SAXBuilder();
          Document doc = builder.build("pois.xml");
          Element root = doc.getRootElement();
          List children = root.getChildren();
          for(int i=0;i<children.size();i++){
              Element el = (Element) children.get(i);
              double id = Double.parseDouble(el.getChildText("POIId"));
              String description = el.getChildText("Description");
              float latitude = Float.parseFloat(el.getChildText("Latitude"));
              float longitude = Float.parseFloat(el.getChildText("Longitude"));
              String type = el.getChildText("Type");
              int ticket = 0;
              if(type.compareTo("Restaurant")==0 || type.compareTo("Café")==0){
                  if(type.compareTo("Restaurant")==0)
                      ticket=1;
                  String telephone = el.getChildText("Telephone");
                  String address = el.getChildText("Address");
                  String characteristics = el.getChildText("Characteristics");
                  int accomodation = Integer.parseInt(el.getChildText("Accomodation"));
                  float min_price = Float.parseFloat(el.getChildText("Min_Price"));
                  float max_price = Float.parseFloat(el.getChildText("Max_Price"));
                  float avg_price = Float.parseFloat(el.getChildText("Avg_Price"));
                  stmt.execute("INSERT INTO eating VALUES ('"+id+"','"+telephone+"','"+address+"','"+min_price+"','"+max_price+"','"+avg_price+"','"+characteristics+"','"+accomodation+"','"+description+"','"+ticket+"','"+latitude+"','"+longitude+"','"+type+"')");
              }
              else if(type.compareTo("Hotel")==0 || type.compareTo("Residência")==0){
                  ticket=1;
                  float min_price_night = Float.parseFloat(el.getChildText("Min_Price_Night"));
                  float max_price_night = Float.parseFloat(el.getChildText("Max_Price_Night"));
                  int num_available_rooms = Integer.parseInt(el.getChildText("Num_Available_Rooms"));
                  int lunch_included = Integer.parseInt(el.getChildText("Lunch_Included"));
                  stmt.execute("INSERT INTO resting VALUES ('"+id+"','"+min_price_night+"','"+max_price_night+"','"+num_available_rooms+"','"+lunch_included+"','"+description+"','"+ticket+"','"+latitude+"','"+longitude+"','"+type+"')");
              }
              else if(type.compareTo("Camping")==0 || type.compareTo("Monument")==0 ||type.compareTo("Park")==0){
                  ticket=0;
                  String country = el.getChildText("Country");
                  String city = el.getChildText("City");
                  float price = Float.parseFloat(el.getChildText("Price"));
                  stmt.execute("INSERT INTO travel VALUES ('"+id+"','"+country+"','"+city+"','"+price+"','"+description+"','"+ticket+"','"+latitude+"','"+longitude+"','"+type+"')");

              }
              System.out.println(id);
          }

          
        }catch (Exception e) {
            e.printStackTrace();
        }


    }
}
